const connection = require('../app/database')

class MomentService {
  // 添加动态
  async create(content, userId) {
    const statement = 'INSERT INTO `moment`(content, user_id) VALUES(?,?);'
    const [result] = await connection.execute(statement,[content,userId])
    return result
  }

  // 查询动态列表
  async queryList(offset = 0, size = 10) {
    const statement = 
    `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime, 
    JSON_OBJECT('id',u.id, 'name', u.name, 'createTime', u.createAt, 'updateTime', u.updateAt) AS user,
    (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id ) commentCount
    FROM moment m 
    LEFT JOIN user u 
    ON u.id = m.user_id
    LIMIT ? OFFSET ?;`
    const [result] = await connection.execute(statement,[String(size),String(offset)])
    return result
  }

  // 获取某一个ID
  async queryById(momentId) {
    const statement = 
    `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime, 
    JSON_OBJECT('id',u.id, 'name', u.name, 'createTime', u.createAt, 'updateTime', u.updateAt) AS user,
    (
      JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt, 'author', JSON_OBJECT('id', cu.id, 'name', cu.name)))
    ) comments
    FROM moment m 
    LEFT JOIN user u ON u.id = m.user_id
    LEFT JOIN comment c ON c.comment_id = m.id
    LEFT JOIN user cu ON cu.id = c.user_id
    WHERE m.id = ?
    GROUP BY m.id;`
    const [result] = await connection.execute(statement,[momentId])
    return result
  }

  // 更新内容
  async update(content,momentId) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?`
    const [result] = await connection.execute(statement, [content, momentId])
    return result
  }

  // 删除操作
  async delete(momentId) {
    const statement = `DELETE FROM moment WHERE id = ?`
    const [result] = await connection.execute(statement, [momentId])
    return result
  }
}

module.exports = new MomentService()
